create database LibraryDb
go
use LibraryDb
go


/*
	学生表
*/
CREATE TABLE Student (
	StudentCode nvarchar(8) primary key,  --学号
	StudentName nvarchar(20) NOT NULL,		--姓名
	Gender bit DEFAULT NULL,	--性别
	Birthday date DEFAULT NULL,	--出生年月
	School nvarchar(20) DEFAULT NULL,	--学院
	Major nvarchar(20) DEFAULT NULL,	--专业
);
go

/*
	学生扩展信息表
*/
CREATE TABLE StudentExtInfo(
	StudentCode nvarchar(8) primary key foreign key references Student(StudentCode),		--学号
	Hobby	nvarchar(120) DEFAULT NULL,		--爱好
	Speciality	nvarchar(120) DEFAULT NULL,	--特长
	OriginPosition	nvarchar(16) DEFAULT NULL,		--生源地
	Prize int DEFAULT NULL,		--荣誉数
);
go


/*
	图书信息表
*/
CREATE TABLE BookInfo (
	ISBN nvarchar(13) primary key,	--ISBN
	BookName nvarchar(32) DEFAULT NULL,	--图书名
	Author nvarchar(32) DEFAULT NULL,	--作者
	Publishing nvarchar(32) DEFAULT NULL,	--出版社
	PublishedTime date DEFAULT NULL,		--出版时间
	Introduce nvarchar(200) DEFAULT NULL,	--书籍简介
	Category nvarchar(8) DEFAULT NULL,	--书籍分类
	Price money DEFAULT NULL,	--书籍价格
);
go


/*
	图书类别表
*/
CREATE TABLE BookExtInfo(
	Barcode nvarchar(9) primary key,	--barcode
	ISBN nvarchar(13) foreign key references BookInfo(ISBN),	--ISBN
	BookStatus bit default null,	--状态
);


/*
	学生借阅信息
*/
CREATE TABLE BorrowRecord(
	Id int primary key identity(1,1),	--借阅id
	StudentCode nvarchar(8) references Student(StudentCode),		--学号
	Barcode nvarchar(9) references BookExtInfo(Barcode),		--barcode
	BorrowTime datetime DEFAULT NULL,	--借书时间
	ReturnTime datetime DEFAULT NULL,	--还书时间
);
go

insert into Student values('16100101','马诗',1,'1998-03-16','理学院','统计');
insert into Student values('16130201','博文',0,'1998-04-24','信息学院','计算机科学与技术');
insert into Student values('16130203','魏波',1,'1998-10-29','信息学院','计算机科学与技术');
insert into Student values('16130205','黄弘',1,'1998-08-06','信息学院','数据科学与大数据技术');
insert into Student values('17100104','易高明',0,'1999-05-29','理学院','信息与计算科学');
insert into Student values('17100105','万承承',0,'1999-09-11','理学院','信息与计算科学');
insert into Student values('17110101','黄弘',0,'2000-07-25','文法学院','法学');
insert into Student values('17130202','邹睿睿',1,'1998-06-29','信息学院','计算机科学与技术');
insert into Student values('17130204','马又云',1,'1999-03-27','信息学院','数据科学与大数据技术');
insert into Student values('18100103','邓承明',1,'2000-07-25','理学院','信息与计算科学');
go

insert into StudentExtInfo values('16100101','听音乐','艺术特长','北京',2);
insert into StudentExtInfo values('16130201','看小说',null,'湖南',3);
insert into StudentExtInfo values('16130203','硬笔书法','艺术特长','新疆',1);
insert into StudentExtInfo values('16130205','听音乐','艺术特长','北京',2);
insert into StudentExtInfo values('17100104','打篮球',null,'北京',3);
insert into StudentExtInfo values('17100105','编程','科技特长','北京',2);
insert into StudentExtInfo values('17110101','打篮球','科技特长','河北',2);
insert into StudentExtInfo values('17130202','编程','科技特长','天津',3);
insert into StudentExtInfo values('17130204','看电影',null,'北京',1);
insert into StudentExtInfo values('18100103',null,null,'河南',null);
go

insert into BookInfo values('7040409659','大学计算机','李凤霞','高等教育出版社','2014','教育部大学计算机课程改革项目规划教材','TP',28);
insert into BookInfo values('7301046065','刑法学','高明轩','北京大学出版社','2000','刑法_法学高等学校中国教材','O',69);
insert into BookInfo values('7806553312','射雕英雄传','金庸','广州出版社','2018','金庸作品集','I',67.9);
insert into BookInfo values('9788020002207','红楼梦','曹雪芹','人民文学出版社','2008','学术研究或个人阅读都非常合适','I',31.25);
insert into BookInfo values('9787113254100','Python语言及其应用','赵广辉','中国铁道出版社','2019','本书介绍Python语言的基础知识及其在各个领域的具体应用','TP',62.2);
insert into BookInfo values('9787115266156','管理信息系统实用教程(第2版)','王若宾','人民邮电出版社','2012','普通高等教育\十一五\国家级规划教材','TP',36);
insert into BookInfo values('9787115356840','管理信息系统实用教程(第3版)','王若宾','人民邮电出版社','2015','普通高等教育\十一五\国家级规划教材','TP',45);
insert into BookInfo values('9787302252955','人工智能:一种现代的方法(第3版)','黄今夏','清华大学出版社','2011','《人工智能:一种现代的方法(第3版)》为大学计算机教育著名教材系列之一','TP',132.6);
insert into BookInfo values('9787513030953','信息论','田甜','知识产权出版社','2015','文理科公选课指定教材','TP',126);
insert into BookInfo values('9787569302585','计算统计(第2版)','冯新奇','西安交通大学出版社','2018','本书涵盖了计算统计的所有核心内容','TP',67.5);
insert into BookInfo values('9789113268712','新编数据库技术','王若宾','中国铁道出版社','2018','本书重构了课程内容结构','TP',38.5);
go

insert into BookExtInfo values('O924.01','7301046065',0);
insert into BookExtInfo values('O924.02','7301046065',1);
insert into BookExtInfo values('O924.03','7301046065',1);
insert into BookExtInfo values('I13.212','9788020002207',1);
insert into BookExtInfo values('I13.213','9788020002207',1);
insert into BookExtInfo values('I247.56','7806553312',1);
insert into BookExtInfo values('I247.59','7806553312',0);
insert into BookExtInfo values('TP122.32','9787569302585',1);
insert into BookExtInfo values('TP122.33','9787569302585',1);
insert into BookExtInfo values('TP311.11','9787113254100',1);
insert into BookExtInfo values('TP311.12','9787113254100',0);
insert into BookExtInfo values('TP311.13','9787115356840',1);
go

insert into BorrowRecord values('16130203','I247.56','2019-04-09','2019-04-13');
insert into BorrowRecord values('17130204','I247.56','2019-04-15','2019-04-17');
insert into BorrowRecord values('16130205','I247.59','2019-04-17','2019-04-20');
insert into BorrowRecord values('16100101','I247.56','2019-04-17','2019-04-18');
insert into BorrowRecord values('17100105','TP311.11','2019-04-29',null);
insert into BorrowRecord values('16130201','I247.59','2019-05-01','2019-05-20');
insert into BorrowRecord values('17130202','TP311.12','2019-05-03',null);
insert into BorrowRecord values('18100103','I13.212','2019-05-04','2019-05-15');
insert into BorrowRecord values('18100103','I13.213','2019-05-20','2019-05-30');
insert into BorrowRecord values('17110101','O924.01','2019-05-25',null);
go

select * from Student
select * from StudentExtInfo
select * from BookInfo
select * from BookExtInfo
select * from BorrowRecord